﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using QuanLyThuVien.Business;


namespace QuanLyThuVien.DataAccess
{
    class PhieuMuonDAL
    {
        clsDatabase db = new clsDatabase();

        public void ThemPhieuMuon(PhieuMuon Phieumuon)
        {
            db.ThuThiSQL("insert into tbPhieuMuon values (N'" + Phieumuon.MaSach
                + "',N'" + Phieumuon.MaThe
                + "',N'" + Phieumuon.NgayMuon
                + "',N'"+Phieumuon.NgayTra2+"')");
        }

        public void SuaPhieuMuon(PhieuMuon Phieumuon)
        {
            db.ThuThiSQL("update tbPhieuMuon set MaSach=N'" + Phieumuon.MaSach
                + "',NgayMuon=N'" + Phieumuon.NgayMuon
                + "',NgayTra=N'" + Phieumuon.NgayTra1
                + "' where MaThe=N'" + Phieumuon.MaThe + "'");
        }

        public void LoadLenDataGridView(DataGridView dg)
        {
            db.LoadLenDG(dg, "select TenSach,ChuyenNganh,tbSach.MaDauSach,tbPhieuMuon.MaSach,tbPhieuMuon.MaThe,TenDG,NgayMuon,NgayTra from tbSach,tbDauSach,tbPhieuMuon,tbDG,tbThe where tbPhieuMuon.MaSach=tbSach.MaSach and tbSach.MaDauSach=tbDauSach.MaDauSach and tbPhieuMuon.MaThe=tbThe.MaThe and tbThe.MaDG=tbDG.MaDG and NgayTra=N'" + "  /  /" + "'");
        }

        public bool KiemTraMaSach(string MaSach)
        {
            return db.KiemTra("select count(*) from tbPhieuMuon where MaSach=N'" + MaSach + "'");
        }

        public bool KiemTraMaThe(string MaThe )
        {
            return db.KiemTra("select count (*) from tbPhieuMuon where MaThe=N'"+MaThe+"'and NgayTra=N'"+"  /  /"+"'");
        }

        public bool KiemTraNgayMuon(string NgayMuon)
        {
            return db.KiemTra("select count(*) from tbPhieuMuon where NgayMuon=N'"+NgayMuon+"'");
        }

        public void XoaPhieuMuon(PhieuMuon PhieuMuon)
        {
            db.ThuThiSQL("delete from tbPhieuMuon where MaThe=N'" + PhieuMuon.MaThe + "' and MaSach=N'" + PhieuMuon.MaSach + "'and NgayMuon=N'" + PhieuMuon.NgayMuon + "'");
        }

        public void TraSach(PhieuMuon Phieumuon)
        {
            db.ThuThiSQL("update tbPhieuMuon set NgayTra=N'" + Phieumuon.NgayTra1
                + "' where MaSach=N'" + Phieumuon.MaSach
                + "' and MaThe=N'" + Phieumuon.MaThe
                + "'and NgayMuon=N'" + Phieumuon.NgayMuon + "'");
        }

        public void SachMuonNhieuNhat()
        {
 
        }

        public string DemDGMuonSach(string MaDG)
        {
          return db.LoadLenLable("select count(*) from tbPhieuMuon pm,tbThe t where MaDG=N'" + MaDG + "'and t.MaThe=pm.MaThe");
        }

        public string DemSachDuocMuon(string MaSach)
        {
            return db.LoadLenLable("select count(*) from tbPhieuMuon where MaSach=N'" + MaSach + "'");
        }

        public void DGMuonQuaHan(DataGridView dg)
        {
             string da1= db.LoadLenLable("select NgayMuon from tbPhieuMuon");
             DateTime da = DateTime.ParseExact(da1, "dd/MM/yyyy",null);
             DateTime da2 = DateTime.Now.Date;
             MessageBox.Show(da.ToString());
             DateTime da3= da.AddMonths(3);
             if (da3 < da2)
             {
                 db.LoadLenDG(dg, "select TenSach,ChuyenNganh,tbSach.MaDauSach,tbPhieuMuon.MaSach,tbPhieuMuon.MaThe,TenDG,NgayMuon,NgayTra from tbSach,tbDauSach,tbPhieuMuon,tbDG,tbThe where tbPhieuMuon.MaSach=tbSach.MaSach and tbSach.MaDauSach=tbDauSach.MaDauSach and tbPhieuMuon.MaThe=tbThe.MaThe and tbThe.MaDG=tbDG.MaDG and NgayTra=N'" + "  /  /" + "'and NgayMuon='"+da1+"'");
             }
        }

        public void XoatheoMaSach(string MaSach)
        {
            db.ThuThiSQL("delete from tbPhieuMuon where MaSach=N'" + MaSach + "'");
        }

        public void XoaTheoMaDS(string DauSach)
        {
            db.ThuThiSQL("delete from tbPhieuMuon where MaSach in (select MaSach from tbSach, tbDauSach where tbSach.MaDauSach=tbDauSach.MaDauSach and tbDauSach.MaDauSach=N'" + DauSach + "')and NgayTra !=N'" + "  /  /" + "'");
        }
    }
}
